﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace ExcelCompare
{
    public delegate void ProcessHandle(int max, int current, string message);

    public class ExcelUtil
    {
        public static Dictionary<string, DataTable> s_mappingValues = new Dictionary<string,DataTable>();
     
        private Excel.Application _app;
        Excel.Workbook _workbooks;
        private string _path;
        public ExcelUtil(string path)
        {
            _path = path;
            _app = new Excel.Application();
            _app.Visible = false;
            object objOpt = System.Reflection.Missing.Value;
            _workbooks = (Excel.Workbook)_app.Workbooks.Open(path, objOpt, true, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
        }

        public List<string> GetSheets()
        {
            List<string> list = new List<string>();
            Excel.Sheets sheets = _workbooks.Worksheets;
            string sheetNams = string.Empty;
            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }

        public DataTable GetData(string sheetName)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=1';data source=" + _path;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [" + sheetName + "$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();

            DataTable table = ds.Tables[0];
            return table;
        }

        public string GetCellValue(string sheet, int rowIndex, int columnIndex)
        {
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheet)
                {
                    return ((Excel.Range)worksheet.Cells[rowIndex, columnIndex]).Text;
                }
            }
            return string.Empty;
        }

        public List<SourceItem> GetData2(string sheetName, ProcessHandle eHandle)
        {
            List<SourceItem> items = new List<SourceItem>();

            Excel.Worksheet sheet = null;
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheetName)
                {
                    sheet = worksheet;
                    break;
                }
            }

            if (sheet == null)
            {
                return items;
            }

            int col = 9;
            int totalRow = sheet.UsedRange.Cells.Rows.Count;
            for (int row = 5; row <= totalRow; row++)
            {
                Excel.Range textRange = (Excel.Range)sheet.Cells[row, col];
                string text = textRange.Text;
                if (!string.IsNullOrEmpty(text))
                {
                    //int obj = Convert.ToInt32(textRange.Interior.Color);

                    try
                    {
                        string cmpValue1 = Math.Round((double)((Excel.Range)sheet.Cells[row, col + 2]).Value, 2).ToString();
                        string cmpValue2 = Math.Round((double)((Excel.Range)sheet.Cells[row, col + 4]).Value, 2).ToString();
                        SourceItem item = new SourceItem();
                        item.Row = row;
                        item.Col = col;
                        item.Text = text;
                        item.CmpValue1 = cmpValue1;
                        item.CmpValue2 = cmpValue2;

                        //if (obj ==16763904 || obj > 16760000)
                        //{
                        //    item.IsTotal = true;
                        //}
                        //else
                        //{
                        //    item.IsTotal = false;
                        //}

                        items.Add(item);
                    }
                    catch
                    {
                    }
                }
                eHandle(totalRow - 5, row - 5,  string.Format("加载: {0}",text));
            }


            return items;
        }

        public void CompareSourceItem(string sheetName, List<SourceItem> items, ProcessHandle eHandle)   
        {
            Excel.Worksheet sheet = null;
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheetName)
                {
                    sheet = worksheet;
                    break;
                }
            }

            if (sheet == null)
            {
                return;
            }

            int textcol1 = 3;
            int textcol2 = 4;

            int copyValue1Col = 5;
            int copyValue2Col = 13;

            int cmpValue1Col = 11;
            int cmpValue2Col = 15;

            int totalRow = sheet.UsedRange.Cells.Rows.Count;
            for (int row = 1; row <= totalRow; row++)
            {
                string text1 = ((Excel.Range)sheet.Cells[row, textcol1]).Text;
                string text2 = ((Excel.Range)sheet.Cells[row, textcol2]).Text;

                for (int index = 0; index < items.Count; index++)
                {
                    SourceItem item = items[index];
                    if (text1 == item.Text || text2 == item.Text)
                    {
                        try
                        {
                            string cmpValue1 = Math.Round((double)((Excel.Range)sheet.Cells[row, cmpValue1Col]).Value, 2).ToString();
                            string cmpValue2 = Math.Round((double)((Excel.Range)sheet.Cells[row, cmpValue2Col]).Value, 2).ToString();
                            string copyValue1 = ((Excel.Range)sheet.Cells[row, copyValue1Col]).Text;
                            object copyValue2 = ((Excel.Range)sheet.Cells[row, copyValue2Col]).Value;

                            if (item.CmpValue1 == cmpValue1)
                            {
                                item.IsValue1Match = true;
                            }
                            else
                            {
                                item.IsValue1Match = false;
                            }

                            if (item.CmpValue2 == cmpValue2)
                            {
                                item.IsValue2Match = true;
                            }
                            else
                            {
                                item.IsValue2Match = false;
                            }

                            item.CopyValue1 = copyValue1;
                            item.CopyValue2 = copyValue2;
                            //break;
                        }
                        catch
                        {
                        }
                    }
                }
                eHandle(totalRow, row, "正在比较数据");
            }
        }

        public string  SaveAs(string sheetName, List<SourceItem> items, ProcessHandle eHandle)
        {
            Excel.Worksheet sheet = null;
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheetName)
                {
                    sheet = worksheet;
                    break;
                }
            }

            if (sheet == null)
            {
                return string.Empty;
            }
            for (int index = 0; index < items.Count; index++)
            {
                SourceItem item = items[index];

                if (item.IsValue2Match)
                {
                    ((Excel.Range)sheet.Cells[item.Row, item.Col]).Value = item.Text;
                    // cmpValue1 
                    ((Excel.Range)sheet.Cells[item.Row, item.Col + 2]).Value = item.CmpValue1;
                    // cmpValue2
                    ((Excel.Range)sheet.Cells[item.Row, item.Col + 4]).Value = item.CmpValue2;


                    // copyValue1 
                    ((Excel.Range)sheet.Cells[item.Row, item.Col - 7]).Value = item.CopyValue1;
                    // copyValue2
                    ((Excel.Range)sheet.Cells[item.Row, item.Col + 3]).Value = item.CopyValue2;
                }
                eHandle(items.Count, index, string.Format("修改:{0}", item.Text));
            }

            string path = string.Format("{0}.{1}.xls", _path, DateTime.Now.ToString("yyyyMMddHHssmm"));
            sheet.SaveAs(
                path,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing,
                Type.Missing);
            return path;
        }

        public DataTable GetData(string sheetName, int count)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;';data source=" + _path;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT top " + count.ToString() + " * FROM [" + sheetName + "$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();

            DataTable table = ds.Tables[0];
            return table;
        }

        //public DataTable GetSchema()
        //{
        //    string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;';data source=" + _path;
        //    OleDbConnection myConn = new OleDbConnection(strCon);
        //    myConn.Open();

        //    DataTable table = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[]{null,null,"2007$","COLUMN"});
        //    myConn.Close();

        //    return table;
        //}

        public void Close()
        {
            if (_workbooks != null)
            {
                _workbooks.Close(false, Type.Missing, Type.Missing);
                _app.Quit();
            }
        }

        private bool Cmp(string dataTableColumnName, string xmlExcelColumnName)
        {
            int value = Convert.ToInt32(dataTableColumnName.Replace("F", string.Empty));
            int xmlValue = 0;
            for (int index = 0; index < xmlExcelColumnName.Length; index++)
            {
                int c = xmlExcelColumnName[index] - 'A' + 1;

                xmlValue += c * (int)Math.Pow(26, xmlExcelColumnName.Length - index - 1);
            }

            if (value == xmlValue)
            {
                return true;
            }
            return false;
        }
        public static bool ExportExcel(DataTable table, string excelFileName)
        {
            object objOpt = System.Reflection.Missing.Value;
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.Visible = false;
                Excel._Workbook wkb = excel.Workbooks.Add(objOpt);
                Excel._Worksheet wks = (Excel._Worksheet)wkb.ActiveSheet;

                wks.Visible = Excel.XlSheetVisibility.xlSheetVisible;

                int rowIndex = 1;
                int colIndex = 0;

                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
                //excel.Sheets[0] = "sss";
                wkb.SaveAs(excelFileName, objOpt, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                wkb.Close(false, objOpt, objOpt);
            }
            catch
            {
                return false;
            }
            finally
            {
                excel.Quit();
            }

            return true;
        }
    }

    public class AA24
    {
        /// <summary>
        /// 27->AA
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToAA(int value)
        {
            string AA = string.Empty;
            int d = value / 26;
            int remainder = value % 26;

            if (d == 1 && remainder == 0)
            {
                AA = ToA(26) + AA;
                d = 0;
            }
            else
            {
                AA = ToA(remainder) + AA;
            }

            while (d > 0)
            {
                remainder = d % 26;
                d = d / 26;
                AA = ToA(remainder) + AA;
            }
            return AA;
        }

        /// <summary>
        /// 27->F27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToF27(int value)
        {
            return string.Format("F{0}", value);
        }

        /// <summary>
        /// AA->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromAA(string value)
        {
            int V = 0;

            for (int index = 0; index < value.Length; index++)
            {
                int v = FromA(value[index]);
                V += v * (int)Math.Pow(v, index);
            }
            return V;
        }

        /// <summary>
        /// F27->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromF27(string value)
        {
            return Convert.ToInt32(value.Replace("F", string.Empty));
        }

        private static string ToA(int value)
        {
            if (value <= 0)
            {
                return string.Empty;
            }
            return new string((char)('A' + (value - 1)), 1);
        }

        private static int FromA(char value)
        {
            return (int)value - (char)'A' + 1;
        }
    }
}
